package com.dz.risk.service.impl;

import com.baomidou.mybatisplus.core.toolkit.IdWorker;
import com.dz.risk.common.ExcelUtils;
import com.dz.risk.exception.VerifyException;
import com.dz.risk.pojo.domain.ReportFormsDataValueEntity;
import com.dz.risk.pojo.domain.ReportFormsEntity;
import com.dz.risk.pojo.model.FormulaModel;
import com.dz.risk.service.DynamicReportFormsService;
import com.dz.risk.service.ReportCalculatedVersionManagementService;
import com.dz.risk.service.ReportFormsDataValueService;
import com.dz.risk.service.ReportFormsService;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.Assert;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

/**
 * 动态表体系业务处理类
 *
 * @author Doke
 * @date 2021/7/19 13:42
 */
@Service
public class DynamicReportFormsServiceImpl implements DynamicReportFormsService {

    private final ReportFormsService reportFormsService;

    private final ReportFormsDataValueService reportFormsDataValueService;

    private final ReportCalculatedVersionManagementService reportCalculatedVersionManagementService;

    @Autowired
    public DynamicReportFormsServiceImpl(ReportFormsService reportFormsService, ReportFormsDataValueService reportFormsDataValueService, ReportCalculatedVersionManagementService reportCalculatedVersionManagementService) {
        this.reportFormsService = reportFormsService;
        this.reportFormsDataValueService = reportFormsDataValueService;
        this.reportCalculatedVersionManagementService = reportCalculatedVersionManagementService;
    }

    /**
     * 动态解析Excel文件信息导入动态表体系
     *
     * @param file              文件
     * @param reportFormsEntity 报表目录信息实体
     * @throws IOException
     */
    @Transactional(rollbackFor = Exception.class)
    @Override
    public void dynamicImportExcel(MultipartFile file, ReportFormsEntity reportFormsEntity) throws IOException {
        //生成报表ID
        String idStr = IdWorker.getIdStr();
        reportFormsEntity.setId(idStr);

        //解析文件插入报表指标项跟报表数据值表
        List<ReportFormsDataValueEntity> reportFormsDataValueEntities = parseExcel(file.getOriginalFilename(), file.getInputStream(), reportFormsEntity.getId());

        //逻辑处理保存报表基本信息
        saveReportFormsLogicalProcessing(reportFormsEntity);

        //保存报表指标值信息
        saveReportFormsDataValue(reportFormsDataValueEntities);
    }

    /**
     * 保存报表基本信息
     *
     * @param reportFormsEntity 报表基本信息实体
     */
    private void saveReportFormsLogicalProcessing(ReportFormsEntity reportFormsEntity) {
        //保存报表基本信息
        reportFormsService.save(reportFormsEntity);
    }

    /**
     * 保存报表指标值数据
     *
     * @param reportFormsDataValues 指标值数据集合
     */
    private void saveReportFormsDataValue(List<ReportFormsDataValueEntity> reportFormsDataValues) {
        Assert.notNull(reportFormsDataValues, "报表内无任何指标值数据");
        reportFormsDataValueService.saveBatch(reportFormsDataValues);
    }

    /**
     * 解析动态Excel文件流
     *
     * @param fileName      文件名
     * @param is            输入流
     * @param reportFormsId 所属报表Id
     * @return 动态表指标项以及指标数据临时模型
     * @throws IOException
     */
    private List<ReportFormsDataValueEntity> parseExcel(String fileName, InputStream is, String reportFormsId) throws IOException {
        Workbook workbook = ExcelUtils.getWorkbook(is, fileName);
        //动态表单元格数据值集合
        List<ReportFormsDataValueEntity> reportFormsDataValueList = new ArrayList<>();

        //获取第一个sheet表
        Sheet sheet = workbook.getSheetAt(0);
        //获取起始行
        int lastRowNum = sheet.getLastRowNum();
        //遍历数据行
        for (int rowNum = 0; rowNum <= lastRowNum; rowNum++) {
            //获取当前遍历行
            Row row = sheet.getRow(rowNum);
            if (Objects.nonNull(row)) {
                //获取当前行的数据起始列
                short firstCellNum = 0;
                short lastCellNum = row.getLastCellNum();

                if (firstCellNum != lastCellNum) {
                    //遍历当前行的所有列
                    for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
                        //获取当前遍历的单元格
                        Cell cell = row.getCell(cellNum);

                        //创建动态表数据内容单元格数据值对象
                        ReportFormsDataValueEntity reportFormsDataValueEntity = new ReportFormsDataValueEntity();
                        reportFormsDataValueEntity
                                .setReportFormsId(reportFormsId)
                                .setLineNumber(rowNum)
                                .setColumnNumber(cellNum);

                        if (Objects.nonNull(cell)) {
                            //获取单元格批注
                            String comments = getComments(cell);
                            reportFormsDataValueEntity.setDataUnit(comments);

                            //获取单元格值
                            String cellValue = String.valueOf(ExcelUtils.getCellValue(cell));
                            //根据单元格值设置单元格数据值及单元格编号
                            setUpReportFormsDataValueByCell(cellValue, reportFormsDataValueEntity);
                        }
                        reportFormsDataValueList.add(reportFormsDataValueEntity);
                    }
                }
            }
        }
        return reportFormsDataValueList;
    }

    /**
     * 根据单元格设置单元格数据值对象
     *
     * @param cellValue                  单元格值
     * @param reportFormsDataValueEntity 单元格数据值对象实体
     */
    private void setUpReportFormsDataValueByCell(String cellValue, ReportFormsDataValueEntity reportFormsDataValueEntity) {
        final String numberPrefix = "M{";
        final String numberSuffix = "}";
        final String horizontalLine = "_";

        //判断单元格是编号还是具体值
        if (Objects.nonNull(cellValue) && cellValue.contains(numberPrefix)) {
            //去掉编号前后缀
            cellValue = cellValue.replace(numberPrefix, "");
            cellValue = cellValue.replace(numberSuffix, "").trim();
            //编码
            String serialNumber;

            //判断是否编号后还拼接了值
            if (cellValue.contains(horizontalLine)) {
                int i = cellValue.indexOf(horizontalLine);

                //截取编码
                serialNumber = cellValue.substring(0, i);

                //设置单元格编号
                reportFormsDataValueEntity.setTableCellSerialNumber(cellValue.substring(0, i));
                //设置单元格值
                reportFormsDataValueEntity.setDataValue(cellValue.substring(i).replace(horizontalLine, "").trim());

                //如果编号后未拼接值
            } else {

                serialNumber = cellValue;
                //设置单元格编号
                reportFormsDataValueEntity.setTableCellSerialNumber(cellValue);
            }

            //根据编号查询该编号以往的公式集合
            reportFormsDataValueEntity.setFormula(getHistoryFormulaBySerialNumber(serialNumber));

        } else {
            reportFormsDataValueEntity.setDataValue(cellValue);
        }

    }

    /**
     * 根据编号获取最近的历史公式
     *
     * @param serialNumber 报表编号
     * @return 公式模型
     */
    private FormulaModel getHistoryFormulaBySerialNumber(String serialNumber) {
        FormulaModel formulaModel = null;
        //根据编号查询该编号以往的公式集合
        List<ReportFormsDataValueEntity> dataValueEntities = reportFormsDataValueService.selectFormula(serialNumber);

        if (!CollectionUtils.isEmpty(dataValueEntities)) {
            formulaModel = dataValueEntities.get(0).getFormula();
        }

        return formulaModel;
    }

    /**
     * 获取单元格批注
     *
     * @param cell 单元格操作对象
     * @return 批注内容
     */
    private String getComments(Cell cell) {
        String comments = null;
        Comment cellComment = cell.getCellComment();
        if (Objects.nonNull(cellComment)) {
            comments = cellComment.getString().getString();
        }
        return comments;
    }

    /**
     * 根据报表ID更新动态报表模板数据<p/>
     * 1.先删除该报表ID下得模板数据以及计算数据<tr/>
     * 2.解析文件插入报表指标项跟报表数据值表<tr/>
     * 3.保存报表模板解析后得报表指标数据集合信息<tr/>
     *
     * @param file              文件
     * @param reportFormsEntity 报表基本信息
     */
    @Transactional(rollbackFor = Exception.class)
    @Override
    public void updateDynamicExcel(MultipartFile file, ReportFormsEntity reportFormsEntity) throws IOException {
        Assert.notNull(file, "文件不能为空");

        //1.先删除该报表ID下得模板数据以及计算数据
        reportFormsDataValueService.deleteByReportFormsId(reportFormsEntity.getId());

        //2.解析文件插入报表指标项跟报表数据值表
        List<ReportFormsDataValueEntity> reportFormsDataValueEntities = parseExcel(file.getOriginalFilename(), file.getInputStream(), reportFormsEntity.getId());

        //3.保存报表模板解析后得报表指标数据集合信息
        saveReportFormsDataValue(reportFormsDataValueEntities);
    }

    /**
     * 根据报表ID删除报表版本数据以及模板数据
     *
     * @param reportFormsId 报表ID
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public void deleteByReportFormsId(String reportFormsId) {
        //根据报表ID删除下属报表指标数据
        reportFormsDataValueService.deleteByReportFormsId(reportFormsId);

        //删除数据库该ID得报表基本信息
        reportFormsService.removeById(reportFormsId);

        //根据报表ID删除报表计算记录
        reportCalculatedVersionManagementService.deleteByReportFormsId(reportFormsId);
    }
}
